<?php
/**
 * extention.func.php   用户自定义函数库
 *
 * @author           袁志蒙  
 * @license          http://www.yzmcms.com
 * @lastmodify       2018-03-18
 */

//聚水潭列表
function get_jst_list($key = ''){
	if(!$jst_list = getcache('JST_CONFIG::jst_list')){
		$data = D('jst_list')->where(array('status'=>1))->select();
		$jst_list = array();
		foreach($data as $val){
			$jst_list[$val['id']] = $val;
		}
		setcache('JST_CONFIG::jst_list', $jst_list);
	}
    if(!$key){
		return $jst_list;
	}else{
		return array_key_exists($key, $jst_list) ? $jst_list[$key] : '';
	}	
}

//所有品牌列表
function get_brand_list(){
	if(!$brand_list = getcache('JST_CONFIG::brand_list')){
		$brand_list = D('jst_brand')->field('name')->where(array('status'=>1))->order('sort asc')->select();
		$brand_list =array_column($brand_list,'name');
		setcache('JST_CONFIG::brand_list', $brand_list);
	}
	return $brand_list;
}

//所有管理员
function get_admin_list(){
	if(!$admin_list = getcache('CONFIG::admin_list')){
		$data = D('admin')->field('adminid,adminname')->select();
		$admin_list = array();
		foreach($data as $val){
			$admin_list[$val['adminid']] = $val['adminname'];
		}
		setcache('CONFIG::admin_list', $admin_list);
	}
	return $admin_list;
}

//佑晟品牌列表
function get_brand_list_ys(){
	if(!$brand_list_ys = getcache('JST_CONFIG::brand_list_ys')){
		$brand_list_ys = D('jst_brand')->field('name')->where(array('status'=>1,'is_ys'=>1))->order('sort asc')->select();
		$brand_list_ys =array_column($brand_list_ys,'name');
		setcache('JST_CONFIG::brand_list_ys', $brand_list_ys);
	}
	return $brand_list_ys;
}

//品牌商品资料绑定聚水潭
function get_brand_by_jst($jst_id){
	if(!$brand_by_jst = getcache('JST_CONFIG::brand_by_jst_'.$jst_id)){
		$list = D('jst_brand')->field('name')->where(array('status'=>1,'jst_id'=>$jst_id))->order('sort asc')->select();
		$brand_by_jst =array_column($list,'name');
		setcache('JST_CONFIG::brand_by_jst_'.$jst_id, $brand_by_jst);
	}
	return $brand_by_jst;
}

//所有店铺列表
function get_shop_list(){
	if(!$shop_list = getcache('JST_CONFIG::shop_list')){
		$shop_list = D('jst_shop')->field('shop_id')->where(array('status'=>1))->order('sort asc')->select();
		$shop_list =array_column($shop_list,'shop_id');
		setcache('JST_CONFIG::shop_list', $shop_list);
	}
	return $shop_list;
}

//佑晟店铺列表id
function get_shop_list_ys(){
	if(!$shop_list_ys = getcache('JST_CONFIG::shop_list_ys')){
		$shop_list_ys = D('jst_shop')->field('shop_id')->where(array('status'=>1,'is_ys'=>1))->order('sort asc')->select();
		$shop_list_ys =array_column($shop_list_ys,'shop_id');
		setcache('JST_CONFIG::shop_list_ys', $shop_list_ys);
	}
	return $shop_list_ys;
}

//库存保存聚水潭id
function get_brand_by_jst2($brand_name){
	if(!$brand_list = getcache('JST_CONFIG::brand_list_jst2')){
		$list = D('jst_brand')->field('name,jst_id2')->where(array('status'=>1))->order('sort asc')->select();
		$brand_list = [];
		foreach($list as $v){
			$brand_list[$v['name']] = $v['jst_id2'];
		}
		setcache('JST_CONFIG::brand_list_jst2', $brand_list);
	}
	//Jeep佐佑云仓报错
	return $brand_list[$brand_name]??0;
}

//库存保存聚水潭id列表
function get_brand_by_jst3(){
	if(!$brand_list = getcache('JST_CONFIG::brand_list_jst3')){
		$list = D('jst_brand')->field('DISTINCT(jst_id2)')->where(array('status'=>1))->order('sort asc')->select();
		$brand_list =array_column($list,'jst_id2');
		setcache('JST_CONFIG::brand_list_jst3', $brand_list);
	}
	return $brand_list;
}

//仓库绑定聚水潭
function get_wms_jst($wms_co_id){
	if(!$wms_jst = getcache('JST_CONFIG::wms_jst')){
		$data = D('jst_wms')->field('wms_co_id,jst_id')->where(array('is_enable'=>1))->select();
		$wsm_jst = array();
		foreach($data as $val){
			$wsm_jst[$val['wms_co_id']] = $val['jst_id'];
		}
		setcache('JST_CONFIG::wms_jst', $wsm_jst);
	}
	return $wms_jst[$wms_co_id]??'';
}

//仓库绑定聚水潭
function get_wms_name($wms_co_id){
	if(!$wms_jst = getcache('JST_CONFIG::get_wms_name')){
		$data = D('jst_wms')->field('name,wms_co_id')->where(array('is_enable'=>1))->select();
		$wsm_jst = array();
		foreach($data as $val){
			$wsm_jst[$val['wms_co_id']] = $val['name'];
		}
		setcache('JST_CONFIG::get_wms_name', $wsm_jst);
	}
	return $wms_jst[$wms_co_id]??'';
}

//所有店铺列表
function get_shop_all(){
	if(!$shop_list = getcache('JST_CONFIG::shop_list_all')){
		$data = D('jst_shop')->where(array('status'=>1))->order('sort asc')->select();
		$shop_list = array();
		foreach($data as $val){
			$shop_list[$val['shop_id']] = $val;
		}
		setcache('JST_CONFIG::shop_list_all', $shop_list);
	}
	return $shop_list;
}

//分类名称,缓存
function get_category_names(){
	if(getcache('JST_CONFIG::category_names')){
		return getcache('JST_CONFIG::category_names');
	}
	$category_list = D('jst_sku')->field('category,count(1) count')->group('category')->order('count desc')->select();
	$category_names = [];
	foreach($category_list as $category){
		$category_name = [];
		$category_name['category'] = $category['category'];
		$category_name['count'] = $category['count'];
		$category_names[] = $category_name;
	}
	setcache('JST_CONFIG::category_names', $category_names,86400);
	return $category_names;
}

//供应商,缓存
function get_supplier_names(){
	if(getcache('JST_CONFIG::supplier_names')){
		return getcache('JST_CONFIG::supplier_names');
	}
	$supplier_list = D('jst_sku')->field('supplier_name,count(1) count')->group('supplier_name')->order('count desc')->select();
	$supplier_names = [];
	foreach($supplier_list as $supplier){
		$supplier_name = [];
		$supplier_name['supplier_name'] = $supplier['supplier_name'];
		$supplier_name['count'] = $supplier['count'];
		$supplier_names[] = $supplier_name;
	}
	setcache('JST_CONFIG::supplier_names', $supplier_names,86400);
	return $supplier_names;
}

//趋势平均值
function avg_trend($days1,$days2){
	if($days2<1||($days1<9&&$days2<9)){
		return 0;
	}
	$avg = round(($days1-$days2)/$days2,2)*100;
	if(abs($avg)>=20){
		return $avg;
	}
	return 0;
}

function get_item_timer($jst_id,$param){
	$d = D('jst_log');
	$page_index = 1;
	//if(!$log_info = getcache('JST_LOG::item_'.$jst_id)){
		$log_info = $d->where(['type'=>'item','jst_id'=>$jst_id,'modified_begin>'=>0,'status'=>1])->order('id desc')->find();
	//}
	if(isset($log_info['modified_begin'])){
		//如果不是最新数据（近30分钟），按天获取
		if($log_info['has_next']>0){
			$begin = $log_info['modified_begin'];
			$end = $log_info['modified_end'];
			$page_index = $log_info['page_index']+1;
		}else{
			$begin = $log_info['modified_end'];
			if(time()-$log_info['modified_end']>30*60){
				$end = strtotime(date('Y-m-d',$begin))+86400;//按天获取
				//$end = $begin+86400;//按天获取
			}else{
				$end = time();
			}
		}
	}else{
		//如果没有数据，使用开始使用聚水潭系统作为开始时间。按天获取
		$jst = get_jst_list($jst_id);
		$begin = $jst['start_time'];
		$end = $begin+86400;
	}
	$insert_data = ['page_index'=>$page_index,'modified_begin'=>date('Y-m-d H:i:s',$begin),'modified_end'=>date('Y-m-d H:i:s',$end)];
	$insert_data['jst_id'] = $jst_id;
	$insert_data['add_time'] = time();
	$msg = $insert_data['modified_begin'].'~'.$insert_data['modified_end'];
	$params = array_merge($insert_data,$param);
	$jst = new jst($jst_id);
	$result = $jst->item($params);
	if($result['code']==0){
		//处理数据
		$result = $result['data'];
		//page_index超过800,分段
		if($result['page_count']>800){
			$end = ($end-$begin)/2+$begin;
			$insert_data['status'] = 1;
			$insert_data['has_next'] = 1;
			$insert_data['page_index'] = 0;
			$msg .='，分页超过800页，分页处理';
		}else{
			if(isset($result['datas'])&&!empty($result['datas'])){
				$insert_data['has_next'] = $result['has_next'];
				$return = get_item_data($jst_id,$result['datas']);
				$msg .= $return['msg'];
				$insert_data['status'] = $return['status'];
			}else{
				$insert_data['has_next'] = 0;
				$insert_data['status'] = 1;
				$msg .= '，无数据';
			}
		}
		$insert_data['page_count'] = $result['page_count'];
	}else{
		$msg .= ',接口错误信息：'.$result['msg'];
		$insert_data['status'] = 0;		
	}
	$insert_data['msg'] = $msg;
	$insert_data['type'] = 'item';
	$insert_data['modified_begin'] = $begin;
	$insert_data['modified_end'] = $end;
	$r = $d->insert($insert_data);
	return ['status'=>$insert_data['status'],'msg'=>$msg];
}

//根据i_id获取商品
function reget_item_timer($jst_id,$param){
	$insert_data['jst_id'] = $jst_id;
	$params = array_merge($insert_data,$param);
	$jst = new jst($jst_id);
	$result = $jst->item($params);
	if($result['code']==0){
		//处理数据
		$result = $result['data'];
		$i_ids = array_column($result['datas'],'i_id');
		//对比数组
		sort($i_ids);
		sort($param['i_ids']);
		$difference = array_diff($param['i_ids'],$i_ids);
		if(!empty($difference)){
			$difference = implode('","',$difference);
			$res = D('jst_sku')->delete('i_id in("'.$difference.'")');
			//删除无用商品属性
			$res = D('jst_sku_ups')->delete('i_id in("'.$difference.'")');
			if($res===false){
				$msg = '删除i_id商品失败："'.$difference.'"，';
			}else{
				$msg = '删除i_id商品："'.$difference.'"，';
			}
		}else{
			$msg = '无需删除，';
		}
		if(isset($result['datas'])&&!empty($result['datas'])){
			$return = get_item_data($jst_id,$result['datas']);
			$msg .= $return['msg'];
			$insert_data['status'] = $return['status'];
			//部分商品上传错品牌，后续变更品牌的。删除
			if(strpos($return['msg'],'插入0，更新0')){
				D('jst_sku')->delete('i_id in("'.implode('","',$param['i_ids']).'")');
			}
			//删除没有更新的商品资料，比如多出来商品资料
			$res = D('jst_sku')->delete('i_id in("'.implode('","',$i_ids).'") and add_time<'.time()-86400);
			if($res){
				$msg .='删除部分没有用的商品编码';
			}
			
		}else{
			$insert_data['has_next'] = 0;
			$insert_data['status'] = 1;
			$msg .= '，无数据';
		}
	}else{
		$msg = ',接口错误信息：'.$result['msg'];
		$insert_data['status'] = 0;		
	}
	$insert_data['msg'] = $msg;
	$insert_data['type'] = 'item';
	$insert_data['add_time'] = time();
	$r = D('jst_log')->insert($insert_data);
	return ['status'=>$insert_data['status'],'msg'=>$msg];
}

//获取商品库存
function get_inventory_timer($jst_id,$param){
	$insert_data['jst_id'] = $jst_id;
	$params = array_merge($insert_data,$param);
	$params['sku_ids'] = implode(',',$params['sku_ids']);
	$jst = new jst($jst_id);
	$result = $jst->inventorys($params);
	$update = 0;
	$insert = 0;
	$num = 0;
	//有获取到库存的商品列表,没获取到删除
	$temp_sku_list = [];
	if($result['code'] == 0){
		$num = count($result['data']['inventorys']);
		if($num>0){
			foreach($result['data']['inventorys'] as $v){
				$temp_sku_list[] = $v['sku_id'];
				$msg = insert_inventory($v,$jst_id);
				if($msg['code'] == 1&&isset($msg['type'])){
					if($msg['type'] == 'update'){
						$update++;
					}else{
						$insert++;
					}
				}
			}
		}
		$msg = '共获取'.$num.'条数据，更新'.$update.'条，插入'.$insert.'条，';
		$insert_data['status'] = 1;	
	}else{
		$msg = '接口错误信息：'.$result['msg'];
		$insert_data['status'] = 0;	
	}
	//部分商品没有获取到库存,删除库存记录
	sort($temp_sku_list);
	sort($param['sku_ids']);
	$difference = array_diff($param['sku_ids'],$temp_sku_list);
	if(!empty($difference)){
		$difference = implode('","',$difference);
		$res = D('jst_inventorys')->delete('sku_id in("'.$difference.'")');
		if($res===false){
			$msg .= '删除sku_id库存失败："'.$difference.'"，';
		}else{
			$msg .= '删除i_id库存："'.$difference.'"，';
		}
	}else{
		$msg .= '无需删除，';
	}
	$insert_data['msg'] = $msg;
	$insert_data['type'] = 'inventorys';
	$insert_data['add_time'] = time();
	$r = D('jst_log')->insert($insert_data);
	$r2 = D('jst_sku')->update(['inventory_syn_time'=>time()],'sku_id in ("'.implode('","',$param['sku_ids']).'")');
	return ['status'=>$insert_data['status'],'msg'=>$msg];
}

//商品插入数据库
function get_item_data($jst_id,$datas){
	$d = D('jst_sku');
	$d_ups = D('jst_sku_ups');
	$count_insert = 0;//插入计数器
	$count_update = 0;//更新计数器
	$status = 1;
	$insert_data_all = [];
	$insert_ups_datas = [];
	$d->start_transaction();
	$jst = get_jst_list($jst_id);
	try{
		if(empty($datas)) throw new PDOException('商品资料为空，无需插入数据！');
		$msg = $jst['short_name'].'，共获取'.count($datas).'条款式记录';
		foreach($datas as $v){
			//抓取商品时，跳过不抓取的商品品牌
			if(!in_array($v['brand'],get_brand_by_jst($jst_id))){
				continue;
			}
			$insert_data = get_need_field('item');
			$need_field = array_keys($insert_data);
			$insert_data['created'] = strtotime($v['created']);
			$insert_data['modified'] = strtotime($v['modified']);
			$insert_data['jst_id'] = $jst_id;
			foreach($v['skus'] as $key => $val){
				foreach($val as $key1=>$val1){
					if(in_array($key1,$need_field)){
						$insert_data[$key1] = $val1;
					}
				}
				$sku = $d->where(['sku_id'=>$val['sku_id']])->find();
				$insert_data['category'] = $val['category']??'未知';
				$insert_data['jst_id2'] = get_brand_by_jst2($val['brand']);
				$insert_data['add_time'] = time();
				if($insert_data['remark']){
					$insert_data['remark'] = preg_replace("/\s|　/","",$insert_data['remark']);//删除所有空白字符
				}
				if($sku){
					$res_update = $d->update($insert_data, ['sku_id'=>$val['sku_id']]);
					if($res_update === false) throw new PDOException($v['sku_id'].'更新商品资料失败！');
					$count_update++;
				}else{
					$count_insert++;
					$insert_data_all[] = $insert_data;
				}
				sku_child($val);
			}
			//部分商品存在只有款式，没有商品详情资料，跳过不执行插入
			if(!empty($v['ups'])){
				$insert_ups = [];
				foreach($v['ups'] as $val){
					//只采集面料成份属性
					if($val['p_name']=='面料成份'){
						//部分款式没有面料成分
						if(!isset($val['pv_value'])){
							$val['pv_value'] = '';
						}else{
							$val['pv_value'] = preg_replace("/\s|　/","",$val['pv_value']);//删除所有空白字符
						}
						$insert_ups['i_id'] = $v['i_id'];
						$insert_ups['pv_value'] = $val['pv_value'];
						$insert_ups['add_time'] = time();
						$insert_ups['jst_id'] = $jst_id;
					}elseif($val['p_name']=='季节'){

					}
				}
				if(!empty($insert_ups)){
					$ups = $d_ups->where(['i_id'=>$v['i_id'],'jst_id'=>$jst_id])->find();
					//存在属性更新，不存在加入插入存放数组
					if($ups){
						//属性一样不用更新
						if($ups['pv_value']!=$insert_ups['pv_value']){
							$res_update = $d_ups->update($insert_ups, ['id'=>$ups['id']]);
							if($res_update === false) throw new PDOException($v['sku_id'].'更新商品资料失败！');
						}
					}else{
						$insert_ups_datas[] = $insert_ups;
					}
				}
			}
		}
		if(!empty($insert_ups_datas)){
			$res = $d_ups->insert_all($insert_ups_datas);
			if($res === false) throw new PDOException('插入面料成分失败！');
		}
		if(!empty($insert_data_all)){
			$res = $d->insert_all($insert_data_all);
			if($res === false) throw new PDOException('插入商品资料失败！');
		}
		$msg .='，插入'.$count_insert.'，更新'.$count_update;
		$d->commit();
	}catch(Exception $e){
		$d->rollback();
		$status = 0;
		$msg = $e->getMessage();
	}
	return ['status'=>$status,'msg'=>$msg];
}

//更新订单商品、库存商品信息（sku）
function sku_child($data){
	//更新库存信息
	D('jst_inventorys')->update([
		'sku_name' => $data['name'],
		'other_2' => $data['other_2'],
		'brand' => $data['brand']
	],[
		'sku_id'=>$data['sku_id']
	]);
}

//获取订单
function get_orders_timer($jst_id,$param){
	$d = D('jst_log');
	//if(!$log_info = getcache('JST_LOG::orders_'.$jst_id)){
		$log_info = $d->where(['type'=>'orders','jst_id'=>$jst_id,'modified_begin>'=>0,'status'=>1,'shop_id'=>$param['shop_id']])->order('id desc')->find();
	//}
	$page_index = 1;
	$jst = get_jst_list($jst_id);
	if(isset($log_info['modified_begin'])){
		//如果不是最新数据（近30分钟），按天获取
		if($log_info['has_next']){
			$begin = $log_info['modified_begin'];
			$end = $log_info['modified_end'];
			$page_index = $log_info['page_index']+1;
		}else{
			$begin = $log_info['modified_end'];
			if(time()-$log_info['modified_end']>30*60){
				$end = $begin+86400;//按天获取
			}else{
				$end = time();
			}
		}
	}else{
		//订单只获取近90天订单
		$begin=strtotime(date("Y-m-d"))-90*86400;
		$end = $begin+86400;
	}

	//超过5天的订单只获取已发货的订单
	if($end < time()-86400*5){
		$param['status'] ='Sent';
	}

	$insert_data = ['page_index'=>$page_index,'modified_begin'=>date('Y-m-d H:i:s',$begin),'modified_end'=>date('Y-m-d H:i:s',$end)];
	$insert_data['jst_id'] = $jst_id;
	$insert_data['add_time'] = time();
	$msg = $insert_data['modified_begin'].'~'.$insert_data['modified_end'];
	
	//一飞云仓jeep，获取全部订单
	$shop_id = $param['shop_id'];
	if($param['shop_id']==17822621){
		unset($param['shop_id']);
	}
	
	$params = array_merge($insert_data,$param);
	$jst = new jst($jst_id);
	$result = $jst->orders($params);
	if($result['code']==0){
		//处理数据
		$result = $result['data'];
		if(isset($result['orders'])&&!empty($result['orders'])){
			$insert_data['has_next'] = $result['has_next'];
			$return = get_orders_data($jst_id,$result['orders']);
			$msg .= $return['msg'];
			$insert_data['status'] = $return['status'];
		}else{
			$insert_data['has_next'] = 0;
			$insert_data['status'] = 1;
			$msg .= '，无数据';
		}
	}else{
		$msg .= ',接口错误信息：'.$result['msg'];
		$insert_data['status'] = 0;		
	}
	$insert_data['msg'] = $msg;
	$insert_data['type'] = 'orders';
	$insert_data['modified_begin'] = $begin;
	$insert_data['modified_end'] = $end;
	$insert_data['shop_id'] = $shop_id;
	$r = $d->insert($insert_data);
	
	//插入成功缓存请求记录
	if($insert_data['status'] == 1){
		setcache('JST_LOG::orders_'.$jst_id,$insert_data);
	}
	return ['status'=>$insert_data['status'],'msg'=>$msg];
}

//订单插入数据库
function get_orders_data($jst_id,$datas){
	$d = D('jst_orders');
	$count_insert = 0;//插入计数器
	$status = 1;
	$insert_data_all = [];
	$jst = get_jst_list($jst_id);
	//先删除订单信息
	$o_ids = [];
	if(empty($datas)) return ['status'=>1,'msg'=>'数据为空无需插入数据'];
	foreach($datas as $v){
		$shop_id = $v['shop_id'];
		$o_ids[] = $v['o_id'];
	}
	$res_del = 1;
	if(!empty($o_ids)){
		$res_del = $d->delete('shop_id='.$v['shop_id'].' and o_id in('.implode(',',$o_ids).')');
	}
	if($res_del!==false){
		$d->start_transaction();
		try{
			$msg = $jst['short_name'].'，共获取'.count($datas).'条订单记录';
			foreach($datas as $v){
				//抓取订单时，跳过不抓取的订单
				if(!in_array($v['shop_id'],get_shop_list())){
					continue;
				}
				
				$insert_data = get_need_field('orders');
				$need_field = array_keys($insert_data);
				foreach($v as $key1=>$val1){
					if(in_array($key1,$need_field)&&$val1){
						$insert_data[$key1] = $val1;
					}
				}
				$insert_data['created'] = strtotime($v['created']);
				$insert_data['modified'] = strtotime($v['modified']);
				$insert_data['add_time'] = time();
				$insert_data['send_date'] = $v['send_date']?strtotime($v['send_date']):0;
				$insert_data['order_date'] = $v['order_date']?strtotime($v['order_date']):0;
				$insert_data['jst_id'] = $jst_id;
				$insert_data['status_code'] = get_orders_status_code($v['status']);
				
				$insert_data_items = get_need_field('orders_items');
				$insert_data = array_merge($insert_data,$insert_data_items);
				$need_field_items = array_keys($insert_data_items);
				
				//超过7天订单，不再抓取取消订单。
				if($insert_data['order_date'] < time()-7*86400&&$insert_data['status_code'] == 10){
					continue;
				}
				foreach($v['items'] as $key => $val){
					//佑晟聚水潭、西罗聚水潭全部抓取，其他判断是否有商品存在
					if($jst['short_name']!='佑晟'||$jst['short_name']!='西罗'){
						$sku = D('jst_sku')->where(['sku_id'=>$val['sku_id']])->find();
						if(!$sku) continue;
					}
					foreach($val as $key1=>$val1){
						if(in_array($key1,$need_field_items)){
							$insert_data[$key1] = $val1;
						}
					}
					$count_insert++;
					//聚水潭bug，一个数据包存在多条数据同一订单
					$insert_data_all[] = $insert_data;
				}
			}
			if(!empty($insert_data_all)){
				$res = $d->insert_all($insert_data_all);
				if($res === false) throw new PDOException('插入订单失败！');
			}
			$msg .='，删除'.$res_del.'条数据'.'，插入'.$count_insert;
			$d->commit();
		}catch(Exception $e){
			$d->rollback();
			$status = 0;
			$msg = $e->getMessage();
		}
	}else{
		return ['status'=>0,'msg'=>'删除订单数据失败！'];;
	}
	return ['status'=>$status,'msg'=>$msg];
}


/*插入库存数据*/
function insert_inventory($arr,$jst_id){
	$d = D('jst_inventorys');
	//对应仓库对应的聚水潭id
	/*
	if(isset($arr['coid'])){
		$jst_id = get_wms_jst($arr['coid']);
		if(!$jst_id){
			return ['code'=>1,'msg'=>'非我仓co_id商品跳过'];
		}
	}*/

	$where = ['sku_id'=>$arr['sku_id'],'jst_id2'=>$jst_id];
	$sku_info = D('jst_sku')->field('id,other_2,name,brand')->where($where)->find();
	$inventorys_info = $d->field('sku_id')->where($where)->one();
	//更新同步库存时间
	D('jst_sku')->update(['inventory_syn_time'=>time()],$where);
	if(!$sku_info){
		return ['code'=>1,'msg'=>'非我仓商品资料跳过'];
	}
	
	
	//删除无用数据
	if($arr['qty'] == 0 && $arr['pick_lock'] == 0 && $arr['return_qty'] == 0 && $arr['purchase_qty'] == 0 && $arr['in_qty'] == 0 && $arr['defective_qty'] == 0 && $arr['order_lock'] == 0 && $arr['virtual_qty'] == 0 ){
		$d->delete($where);
		return ['code'=>1,'msg'=>'无用数据删除'];
	}
		
	$insert_data=[];
	$insert_data['modified'] = strtotime($arr['modified']);
	$insert_data['sku_id'] = $arr['sku_id'];
	if(isset($arr['coid'])){
		$insert_data['wms_co_id'] = $arr['coid'];
	}
	$insert_data['add_time'] = time();
	$insert_data['virtual_qty'] = $arr['virtual_qty'];
	$insert_data['qty'] = $arr['qty'];
	$insert_data['pick_lock'] = $arr['pick_lock'];
	$insert_data['return_qty'] = $arr['return_qty'];
	$insert_data['purchase_qty'] = $arr['purchase_qty'];
	$insert_data['in_qty'] = $arr['in_qty'];
	$insert_data['defective_qty'] = $arr['defective_qty'];
	$insert_data['order_lock'] = $arr['order_lock'];
	$insert_data['brand'] = $sku_info['brand'];
	$insert_data['sku_name'] = $sku_info['name'];
	$insert_data['other_2'] = $sku_info['other_2'];
	$insert_data['jst_id2'] = $jst_id;
	if($inventorys_info){
		$res = $d->update($insert_data,$where);
		$type = 'update';
	}else{
		$res = $d->insert($insert_data);
		$type = 'insert';
	}
	return ['code'=>1,'msg'=>'执行成功','type'=>$type];
}


//获取订单状态编码
function get_orders_status_code($key){
	$arr = ['WaitPay'=>1,'Delivering'=>2,'Merged'=>3,'Question'=>4,'Split'=>5,'WaitOuterSent'=>6,'WaitConfirm'=>7,'WaitFConfirm'=>8,'Sent'=>9,'Cancelled'=>10];
	if($key){
		return $arr[$key];
	}
	return $arr;
}

//获取订单状态
function get_orders_status($key=''){
	$order_status = [
		1 => '待付款',
		2 => '发货中',
		3 => '被合并',
		4 => '异常',
		5 => '被拆分',
		6 => '等供销商|外仓发货',
		7 => '已付款待审核',
		8 => '已客审待财审',
		9 => '已发货',
		10 => '取消'
	];
	if(!$key){
		return $order_status;
	}
	return $order_status[$key];
}

//表对应字段及默认值
function get_need_field($type){
	switch ($type) {
		case 'item':
			$field = ['sku_id'=>'','i_id'=>'','name'=>'','properties_value'=>'','category'=>'','pic'=>'','enabled'=>'','brand'=>'','supplier_name'=>'','sku_code'=>'','other_2'=>'','supplier_sku_id'=>'','remark'=>'','created'=>0,'modified'=>0,'cost_price'=>'0.00'];
			break;
		case 'inventory':
			//     商品编码        款式编码  库存    订单占用        仓库待发       虚拟库存          采购在途         销退仓库存       进货仓库存  	次品库存
			$field = ['sku_id'=>'','i_id'=>'','qty'=>0,'order_lock'=>0,'pick_lock'=>0,'virtual_qty'=>0,'purchase_qty'=>0,'return_qty'=>0,'in_qty'=>0,'defective_qty'=>0,
			//修改时间     安全库存下限 安全库存上限  对应仓库代码    本系统添加时间 其他属性2
			'modified'=>'','min_qty'=>0,'max_qty'=>0,'wms_co_id'=>'','add_time'=>'','other_2'=>''];
			break;
		case 'orders':
			//     快递单号     发货日期    下单时间    问题描述         ERP内部订单号 店铺编号  订单备注     修改时间  多标签  订单创建时间  聚水潭id
			$field = ['l_id'=>'','send_date'=>0,'order_date'=>0,'question_desc'=>'','o_id'=>0,'shop_id'=>0,'remark'=>'','modified'=>0,'labels'=>'','created'=>0,'jst_id'=>0,'order_from'=>'','so_id'=>''];
			break;
		case 'orders_items':
			$field = ['sku_id'=>'','name'=>'','i_id'=>'','properties_value'=>'','pic'=>'','qty'=>0];
			break;			
		case 'allocate':
			//     调拨单号        分仓编号          单据日期         修改时间          状态         调拨出仓库名称      调拨入仓库名称       调拨类型   调拨出仓库编号   调拨入仓库编号
			$field = ['io_id' => '','wms_co_id' => '','io_date' => '','modified' => '','status' => '','warehouse' => '','link_warehouse' => '','type' => '','wh_id' => '','link_wh_id'
			//     调拨入分仓编号    备注            线上单号      创建人
			=> '','link_io_id' => '','remark' => '','so_id' => '','creator_name' => '','add_time'=>''];
			break;
		case 'allocate_items':
			//       调拨单号        商品编码      数量       商品名称       颜色及规格             点数 
			$field = ['io_id' => '','sku_id' => '','qty' => '','name' => '','properties_value'=>'','r_qty' => ''];
			break;
		case 'category':
			//       父级类目id           类目id      修改时间         类目名称
			$field = ['parent_c_id' => '','c_id' => '','modified' => '','name' => ''];
			break;
		default:
			$field = [];
	}
	return $field;
}

/**
* 驼峰命名转下划线命名
* 思路:
* 小写和大写紧挨一起的地方,加上分隔符,然后全部转小写
*/
function uncamelize($camelCaps,$separator='_'){
    return strtolower(preg_replace('/([a-z])([A-Z])/', "$1" . $separator . "$2", $camelCaps));
}


//获取需要导出字段及字段格式
function get_excel_field($type=''){
	$field = [
		'id'=>['name'=>'编号','width'=>10,'format'=>'number','align'=>'center'],
		'sku_id'=>['name'=>'商品编码','width'=>20,'format'=>''],
		'i_id'=>['name'=>'款式编码','width'=>15,'format'=>''],
		'sku_name'=>['name'=>'商品名称','width'=>20,'format'=>''],
		'name'=>['name'=>'商品名称','width'=>20,'format'=>''],
		'properties_value'=>['name'=>'商品属性','width'=>20,'format'=>''],
		'category'=>['name'=>'商品分类','width'=>15,'format'=>''],
		'pic'=>['name'=>'商品图片','width'=>10,'format'=>''],
		'brand'=>['name'=>'品牌','width'=>15,'format'=>'','align'=>'center'],
		'modified'=>['name'=>'修改时间','width'=>20,'format'=>'','align'=>'center'],
		'sku_code'=>['name'=>'国标码','width'=>15,'format'=>'','align'=>'center'],
		'other_2'=>['name'=>'商品别名（其他属性2）','width'=>20,'format'=>''],
		'kuanhao'=>['name'=>'其它属性2/款号','width'=>20,'format'=>''],
		'qty'=>['name'=>'库存数','width'=>8,'format'=>'number','align'=>'center'],
		'virtual_qty'=>['name'=>'虚拟库存','width'=>8,'format'=>'number','align'=>'center'],
		'shop_name'=>['name'=>'店铺名称','width'=>15,'format'=>'','align'=>'center'],
		'o_id'=>['name'=>'ERP内部单号','width'=>15,'format'=>'number','align'=>'center'],
		'so_id'=>['name'=>'线上订单号','width'=>20,'format'=>''],
		'status'=>['name'=>'订单状态','width'=>10,'format'=>'','align'=>'center'],
		'order_date'=>['name'=>'订单时间','width'=>20,'format'=>'','align'=>'center'],
		'send_date'=>['name'=>'发货时间','width'=>20,'format'=>'','align'=>'center'],
		'remark'=>['name'=>'备注','width'=>15,'format'=>'','align'=>'center'],
		'labels'=>['name'=>'商品标签','width'=>15,'format'=>''],
		'order_lock'=>['name'=>'订单占有数','width'=>8,'format'=>'number','align'=>'center'],
		'pick_lock'=>['name'=>'仓库待发数','width'=>8,'format'=>'number','align'=>'center'],
		'purchase_qty'=>['name'=>'采购在途数','width'=>8,'format'=>'number','align'=>'center'],
		'return_qty'=>['name'=>'销退仓库存','width'=>8,'format'=>'number','align'=>'center'],
		'in_qty'=>['name'=>'进货仓库存','width'=>8,'format'=>'number','align'=>'center'],
		'defective_qty'=>['name'=>'次品库存','width'=>8,'format'=>'number','align'=>'center'],
		'xt_qty'=>['name'=>'新塘仓库存','width'=>8,'format'=>'number','align'=>'center'],
		'ts_qty'=>['name'=>'唐狮仓库存','width'=>8,'format'=>'number','align'=>'center'],
		'beihuo_qty'=>['name'=>'备货仓库存','width'=>8,'format'=>'number','align'=>'center'],
		'production_qty'=>['name'=>'生产中','width'=>8,'format'=>'number','align'=>'center'],
		'shengchan_qty'=>['name'=>'建议生产','width'=>8,'format'=>'number','align'=>'center'],
		'allocate_qty'=>['name'=>'调拨途中','width'=>8,'format'=>'number','align'=>'center'],
		'buhuo_total_qty'=>['name'=>'建议补货','width'=>8,'format'=>'','align'=>'center'],
		'today'=>['name'=>'今日销量','width'=>10,'format'=>'number','align'=>'center'],
		'1days'=>['name'=>'昨日销量','width'=>10,'format'=>'number','align'=>'center'],
		'2days'=>['name'=>'近2天销量','width'=>10,'format'=>'number','align'=>'center'],
		'3days'=>['name'=>'近3天销量','width'=>10,'format'=>'number','align'=>'center'],
		'5days'=>['name'=>'近5天销量','width'=>10,'format'=>'number','align'=>'center'],
		'7days'=>['name'=>'近7天销量','width'=>10,'format'=>'number','align'=>'center'],
		'30days'=>['name'=>'近30天销量','width'=>10,'format'=>'number','align'=>'center'],
		'sales_add_time'=>['name'=>'销量统计时间','width'=>20,'align'=>'center'],
		'buhuo_cycel'=>['name'=>'生产周期','width'=>10,'format'=>'number','align'=>'center'],
		'ys_qty'=>['name'=>'库存','width'=>8,'format'=>'number','align'=>'center'],
		'ys_purchase_qty'=>['name'=>'生产中','width'=>8,'format'=>'number','align'=>'center'],
		'd'=>['name'=>'日均','width'=>8,'format'=>'number','align'=>'center'],
		'days_num'=>['name'=>'补货天数','width'=>8,'format'=>'number','align'=>'center'],
		'color'=>['name'=>'颜色','width'=>10,'format'=>'number','align'=>'center'],
		'labels_brand'=>['name'=>'销量标签','width'=>8,'format'=>'','align'=>'center'],
		'trend'=>['name'=>'趋势(%)','width'=>8,'format'=>'','align'=>'center'],
		'purchaser_qty'=>['name'=>'采购中','width'=>8,'format'=>'number','align'=>'center'],
		'purchaser_status'=>['name'=>'订单状态','width'=>10,'format'=>'','align'=>'center'],
		'act_qty'=>['name'=>'实际入库','width'=>8,'format'=>'number','align'=>'center'],
		'purchaser_name'=>['name'=>'采购员','width'=>8,'format'=>'','align'=>'center'],
		'confirm_date'=>['name'=>'审核时间','width'=>20,'format'=>'','align'=>'center'],
		'purchaser_remark'=>['name'=>'跟单记录','width'=>60,'format'=>'','align'=>''],
		'back_qty'=>['name'=>'回货数量','width'=>8,'format'=>'number','align'=>'center'],
		'back_remark'=>['name'=>'回货记录','width'=>60,'format'=>'','align'=>''],
		'po_id'=>['name'=>'采购单号','width'=>10,'format'=>'number','align'=>'center'],
		'purchaser_items'=>['name'=>'采购商品','width'=>100,'format'=>'','align'=>''],
		'supplier_name'=>['name'=>'供应商名称','width'=>15,'format'=>'','align'=>'center'],
		'inventorys'=>['name'=>'库存数','width'=>8,'format'=>'number','align'=>'center'],
		'bcp_purchase_qty'=>['name'=>'半成品采购中','width'=>10,'format'=>'number','align'=>'center'],
		'cp_purchase_qty'=>['name'=>'成品加工中','width'=>10,'format'=>'number','align'=>'center'],
		'bcp_in_qty'=>['name'=>'半成品待入库','width'=>10,'format'=>'number','align'=>'center'],
		'bcp_sku_id'=>['name'=>'半成品商品编码','width'=>20,'format'=>''],
		'sales_day'=>['name'=>'库存可售天数','width'=>10,'format'=>'','align'=>'center'],
		'buhuo_qty'=>['name'=>'建议补货','width'=>10,'format'=>'number','align'=>'center'],
	];
	$need_field = excel_need_field($type);
	//返回所需字段
	$return_field = [];
	foreach($need_field as $v){
		$return_field[$v] = $field[$v];
	}
	return $return_field;
}

function excel_need_field($type){
	switch($type){
		case 'orders':
			$need_field = ['id','o_id','so_id','order_date','send_date','remark','labels','status','sku_id','properties_value','qty','name'];
			break;
		case 'sku':
			$need_field = ['i_id','sku_id','name','other_2','brand','supplier_name','qty','purchase_qty','1days','2days','3days','7days','30days','modified','sales_add_time'];
			break;
		case 'inventorys':
			$need_field = ['id','sku_id','i_id','brand','sku_name','other_2','qty','order_lock','pick_lock','virtual_qty','purchase_qty','return_qty','defective_qty'];
			break;
		case 'ts_buhuo':
			$need_field = ['sku_id','sku_code','name','labels','category','xt_qty','ts_qty','allocate_qty','buhuo_qty','production_qty','shengchan_qty','today','1days','2days','3days','7days','30days','sales_add_time'];
			break;
		case 'ys_dingzuo':
			$need_field = ['kuanhao','name','1days','2days','3days','7days','30days','inventorys','sales_day','bcp_purchase_qty','cp_purchase_qty','bcp_in_qty','buhuo_total_qty','buhuo_cycel','sales_add_time'];
			break;
		case 'ys_dingzuo_total':
			$need_field = ['name','kuanhao','bcp_sku_id','1days','2days','3days','7days','30days','inventorys','sales_day','bcp_purchase_qty','cp_purchase_qty','bcp_in_qty','buhuo_total_qty','buhuo_cycel'];
			break;
		case 'wp_buhuo':
			$need_field = ['i_id','sku_id','name','other_2','purchase_qty','properties_value','brand','labels','labels_brand','qty','virtual_qty','beihuo_qty','d','days_num','buhuo_qty','1days','2days','3days','5days','7days','trend'];
			break;
		case 'purchaser':
			$need_field = ['id','po_id','name','purchaser_status','purchaser_qty','act_qty','purchaser_name','confirm_date','purchaser_remark','purchaser_items'];
			break;
		default:
			$need_field = '其他_';
	}
	return $need_field;
}

//删除文件夹删除uploads/xlswriter文件夹
function deleteDir($dirPath) {
    if (!is_dir($dirPath)) {
        return;
    }
    $files = glob($dirPath . '/*');
    foreach ($files as $file) {
		unlink($file);
    }
}

//需要商品标签的品牌列表
function sku_tag_brand(){
	$brands = get_brand_list();
	//线下品牌
	$brands_offline = ['佑晟共享仓','天钧新塘仓','JEEP半成品','ILOVECHOC','琴曼','GOOGIMS','MYMO','马克华菲 半成品'];
	$arr = [];
    foreach($brands as $k1 => $v1){
        if(!in_array($v1,$brands_offline) ){
            $arr[]=$v1;
        }
    }
	return $arr;
}

//根据品牌获取标签规则
function config_sku_tag($brand){
	//$key = sku_tag_key($brand);
	$sku_tag = getcache('JST_CONFIG::BUHUO::'.$brand);
	if($sku_tag){
		return $sku_tag;
	}
	$sku_tag = D('jst_sku_tag')->where(['brand'=>$brand])->order('brand,days_avg desc')->select();
	if(!$sku_tag){
		$sku_tag = D('jst_sku_tag')->where(['brand'=>'默认'])->order('brand,days_avg desc')->select();
	}
	setcache('JST_CONFIG::BUHUO::'.$brand,$sku_tag);
	return $sku_tag;
}

//头尾码对应编码
function touwei_code($code=''){
	$arr = ['-1'=>'异常码','0'=>'未知','1'=>'小码','2'=>'中码','3'=>'大码','4'=>'均码'];
	if($code!=''){
		return $arr[$code];
	}
	return $arr;
}

//获取品牌备货天数
function brand_beihuo($brand,$labels_brand){
	//$key = sku_tag_key($brand);
	$info = getcache('JST_CONFIG::BUHUO::'.$brand.$labels_brand);
	if($info){
		return $info;
	}
	$info = D('jst_sku_tag')->field('days_num,days')->where(['brand'=>$brand,'tag_name'=>$labels_brand])->find();
	setcache('JST_CONFIG::BUHUO::'.$brand.$labels_brand,$info);
	return $info;
}

//三个视图
function mysql_view(){
	//yzm_jst_inventorys_sku 箱及库位
	$sql1 = 'select `a`.`qty` AS `qty`,`a`.`in_qty` AS `in_qty`,`a`.`purchase_qty` AS `purchase_qty`,`a`.`defective_qty` AS `defective_qty`,`a`.`return_qty` AS `return_qty`,`b`.`other_2` AS `other_2`,`b`.`name` AS `name`,`b`.`pic` AS `pic`,`b`.`sku_id` AS `sku_id`,`b`.`i_id` AS `i_id`,`b`.`properties_value` AS `properties_value`,`a`.`jst_id2` AS `jst_id2`,`a`.`modified` AS `modified`,`b`.`brand` AS `brand` from (`yzm_jst_inventorys` `a` left join `yzm_jst_sku` `b` on((`a`.`sku_id` = `b`.`sku_id`)))';
	
	//yzm_jst_purchaser_items 采购单
	$sql2='select `a`.`sku_id` AS `sku_id`,`a`.`name` AS `name`,`a`.`qty` AS `qty`,`a`.`price` AS `price`,`a`.`i_id` AS `i_id`,`a`.`po_id` AS `po_id`,`a`.`remark` AS `remark`,`a`.`act_qty` AS `act_qty`,`b`.`other_2` AS `other_2`,`b`.`pic` AS `pic` from (`yzm_jst_purchaser_item` `a` left join `yzm_jst_sku` `b` on((`b`.`sku_id` = `a`.`sku_id`)))';
	
	//yzm_jst_sku_sales 很多地方用
	$sql3="select `a`.`id` AS `id`,`a`.`sku_id` AS `sku_id`,`a`.`i_id` AS `i_id`,`a`.`name` AS `name`,`a`.`properties_value` AS `properties_value`,`a`.`category` AS `category`,`a`.`pic` AS `pic`,`a`.`enabled` AS `enabled`,`a`.`brand` AS `brand`,`a`.`supplier_name` AS `supplier_name`,`a`.`modified` AS `modified`,`a`.`sku_code` AS `sku_code`,`a`.`add_time` AS `add_time`,`a`.`created` AS `created`,`a`.`other_2` AS `other_2`,`a`.`labels` AS `labels`,`a`.`jst_id` AS `jst_id`,`a`.`jst_id2` AS `jst_id2`,`a`.`inventory_syn_time` AS `inventory_syn_time`,`a`.`labels_brand` AS `labels_brand`,`a`.`tou_wei` AS `tou_wei`,`a`.`cost_price` AS `cost_price`,substring_index(`a`.`other_2`,'#',1) AS `kuanhao`,ifnull(`b`.`1days`,0) AS `1days`,ifnull(`c`.`2days`,0) AS `2days`,ifnull(`d`.`3days`,0) AS `3days`,ifnull(`f`.`7days`,0) AS `7days`,ifnull(`g`.`30days`,0) AS `30days`,ifnull(`h`.`qty`,0) AS `qty`,ifnull(`h`.`in_qty`,0) AS `in_qty`,ifnull(`h`.`purchase_qty`,0) AS `purchase_qty`,`g`.`sales_add_time` AS `sales_add_time` from ((((((`yzm_jst_sku` `a` left join `yzm_jst_sku_sales_1days` `b` on((`a`.`sku_id` = `b`.`sku_id`))) left join `yzm_jst_sku_sales_2days` `c` on((`a`.`sku_id` = `c`.`sku_id`))) left join `yzm_jst_sku_sales_3days` `d` on((`a`.`sku_id` = `d`.`sku_id`))) left join `yzm_jst_sku_sales_7days` `f` on((`a`.`sku_id` = `f`.`sku_id`))) left join `yzm_jst_sku_sales_30days` `g` on((`a`.`sku_id` = `g`.`sku_id`))) left join `yzm_jst_inventorys` `h` on((`a`.`sku_id` = `h`.`sku_id`)))";
	
	//yzm_jst_sku_sales2 唯品补货流程
	$sql4="select `a`.`id` AS `id`,`a`.`sku_id` AS `sku_id`,`a`.`i_id` AS `i_id`,`a`.`name` AS `name`,`a`.`properties_value` AS `properties_value`,`a`.`category` AS `category`,`a`.`pic` AS `pic`,`a`.`enabled` AS `enabled`,`a`.`brand` AS `brand`,`a`.`supplier_name` AS `supplier_name`,`a`.`modified` AS `modified`,`a`.`sku_code` AS `sku_code`,`a`.`add_time` AS `add_time`,`a`.`created` AS `created`,`a`.`other_2` AS `other_2`,`a`.`labels` AS `labels`,`a`.`jst_id` AS `jst_id`,`a`.`jst_id2` AS `jst_id2`,`a`.`inventory_syn_time` AS `inventory_syn_time`,`a`.`labels_brand` AS `labels_brand`,`a`.`tou_wei` AS `tou_wei`,`a`.`cost_price` AS `cost_price`,substring_index(`a`.`other_2`,'#',1) AS `kuanhao`,ifnull(`b`.`1days`,0) AS `1days`,ifnull(`c`.`2days`,0) AS `2days`,ifnull(`d`.`3days`,0) AS `3days`,ifnull(`f`.`7days`,0) AS `7days`,ifnull(`g`.`30days`,0) AS `30days`,`g`.`sales_add_time` AS `sales_add_time`,ifnull(`h`.`5days`,0) AS `5days` from ((((((`yzm_jst_sku` `a` left join `yzm_jst_sku_sales_1days` `b` on((`a`.`sku_id` = `b`.`sku_id`))) left join `yzm_jst_sku_sales_2days` `c` on((`a`.`sku_id` = `c`.`sku_id`))) left join `yzm_jst_sku_sales_3days` `d` on((`a`.`sku_id` = `d`.`sku_id`))) left join `yzm_jst_sku_sales_7days` `f` on((`a`.`sku_id` = `f`.`sku_id`))) left join `yzm_jst_sku_sales_30days` `g` on((`a`.`sku_id` = `g`.`sku_id`))) left join `yzm_jst_sku_sales_5days` `h` on((`a`.`sku_id` = `h`.`sku_id`)))";
}

//头尾码表
function touwei(){
	$touwei = getcache('CONFIG::touwei');
	if($touwei){
		return $touwei;
	}
	$touwei = D('jst_sku_touwei')->select();
	$temp = [];
	foreach($touwei as $v){
		$temp[$v['chima_num']] = $v;
	}
	setcache('CONFIG::touwei',$temp);
	return $temp;
}